package org.csu.onlineedubackend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.csu.onlineedubackend.entity.Course;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Mapper
public interface CourseMapper extends BaseMapper<Course> {
    //教师获取自己已经上架的课程
//    @Select("SELECT * FROM course WHERE teacher_id = #{teacherId} and status = 1")
    @Select("SELECT * FROM course WHERE teacher_id = #{teacherId}")
    List<Course> selectByTeacherId(Integer teacherId);


    // 查询教师的草稿课程
    @Select("SELECT * FROM course WHERE teacher_id = #{teacherId} AND status != 1")
    List<Course> selectDraftBoxCoursesByTeacherId(@Param("teacherId") Integer teacherId);

    @Select("SELECT COUNT(*) FROM course WHERE teacher_id = #{teacherId}")
    int countByTeacherId(@Param("teacherId") Integer teacherId);

    @Select("""
    SELECT COUNT(DISTINCT cs.student_id)
    FROM course_subscribe cs
    JOIN course c ON cs.course_id = c.id
    WHERE c.teacher_id = #{teacherId}
    """)
    int countStudentsByTeacherId(@Param("teacherId") Integer teacherId);

    @Select("""
    SELECT c.*,
    (SELECT AVG(stars) FROM course_comment WHERE course_id = c.id) AS rating
    FROM course c
    WHERE c.teacher_id = #{teacherId}
      AND status != 0
    ORDER BY c.updated_at DESC
    LIMIT 3
    """)
    List<Course> selectRecentlyUpdatedCoursesByTeacherId(@Param("teacherId") Integer teacherId);


    // 获取各类（status）课程数量
    @Select("SELECT status, COUNT(*) AS count FROM course GROUP BY status")
    List<Map<String, Object>> getCourseCountGroupByStatus();

    // 获得老师收入
    @Select("SELECT IFNULL(SUM(price), 0) FROM course WHERE teacher_id = #{teacherId}")
    BigDecimal selectTotalIncomeByTeacherId(@Param("teacherId") Integer teacherId);

    
    @Select("SELECT * FROM course WHERE category = #{category}")
    List<Course> selectByCategory(String category);
    
    /**
     * 检查学生是否已订阅课程（直接查询student_course表）
     * @param studentId 学生ID
     * @param courseId 课程ID
     * @return 订阅记录数量，大于0表示已订阅
     */
    @Select("SELECT COUNT(*) FROM student_course WHERE student_id = #{studentId} AND course_id = #{courseId} AND status = 'paid'")
    int checkStudentSubscription(Integer studentId, Integer courseId);
    
    /**
     * 检查学生是否已收藏课程（直接查询course_bookmark表）
     * @param studentId 学生ID
     * @param courseId 课程ID
     * @return 收藏记录数量，大于0表示已收藏
     */
    @Select("SELECT COUNT(*) FROM course_bookmark WHERE student_id = #{studentId} AND course_id = #{courseId}")
    int checkStudentBookmark(Integer studentId, Integer courseId);
    
    /**
     * 统计课程订阅人数（直接查询course_subscribe表）
     * @param courseId 课程ID
     * @return 订阅人数
     */
    @Select("SELECT COUNT(*) FROM course_subscribe WHERE course_id = #{courseId}")
    int countCourseSubscriptions(Integer courseId);
    
    /**
     * 检查学生是否已订阅课程（直接查询course_subscribe表）
     * @param studentId 学生ID
     * @param courseId 课程ID
     * @return 订阅记录数量，大于0表示已订阅
     */
    @Select("SELECT COUNT(*) FROM course_subscribe WHERE student_id = #{studentId} AND course_id = #{courseId}")
    int checkCourseSubscription(Integer studentId, Integer courseId);
}
